from fastapi import FastAPI

from fastapi import FastAPI,Form

from starlette.responses import HTMLResponse

from starlette.responses import RedirectResponse

import uvicorn

from pyecharts import options as opts

import json

import os,sys

from pyecharts.components import Table

from pyecharts.options import ComponentTitleOpts

from tinydb import TinyDB, Query

from fastapi import Cookie, FastAPI

from typing import Optional

from starlette.responses import Response

from starlette.staticfiles import StaticFiles
from typing import Union
from openpyxl import load_workbook
from typing import List
from fastapi import FastAPI, File, UploadFile

app = FastAPI()
data = {}

location='static/'

app.mount('/static', StaticFiles(directory=location), name='static')

index='''

 

<html>

 

  <head>

    <meta charset="utf-8">

    <meta http-equiv="X-UA-Compatible" content="IE=edge">

    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->

    <title>Bootstrap 101 Template</title>

    <link rel="shortcut icon" href="/static/gd.ico" type="image/x-icon" />

 

    <!-- Bootstrap -->

    <link rel="stylesheet" href=https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">

 

    <!-- HTML5 shim 和 Respond.js 是为了让 IE8 支持 HTML5 元素和媒体查询（media queries）功能 -->

    <!-- 警告：通过 file:// 协议（就是直接将 html 页面拖拽到浏览器中）访问页面时 Respond.js 不起作用 -->

    <!--[if lt IE 9]>

      <script src=https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js></script>

      <script src=https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js></script>

    <![endif]-->

  </head>

 

<body style="text-align:center;">

<table class="table table-bordered">

{}



</body>

</html>

'''

content='''

        <body>
        <h4><a href="/static/{}">下载</a><a href="/"> 首页</a><a href="/search/{}"> 返回</a></h4>
        <form name="input" action="/search/{}" method="get">
        <input type="text" name="q">
        <input type="submit" value="搜索">
        </form>

        </body>

'''

@app.get("/")

 

def home():
    return RedirectResponse(url='/search')

@app.get("/change/{item_id}")
async def read_user_item(item_id: str,
    row: int, col: int, value: str
):
    item = {"row": row, "col": col, "value": value}
    wb = load_workbook(location+item_id)
    ws = wb.active 
    max_row=ws.max_row
    max_column=ws.max_column
    html='<form action="/commit/{}">'.format(item_id)
    line='''
    <td>{}</td>
    '''
    for col in range(1,max_column+1):
        if col==item['col']:
            cell='<input type="text" class="form-control" name="value" value="{}">'.format(item['value'])
        else:
            cell=ws.cell(row=row, column=col).value
        html=html+line.format(cell)
    html=html+'<input type="hidden" name="row" value={}><input type="hidden" name="col" value={}></table><input type="submit" value="提交"></form>'.format(item['row'],item['col'])


    return HTMLResponse(content=index.format(html))

@app.get("/commit/{item_id}")
async def read_user_item(item_id: str,
    row: int, col: int, value: str
):
    item = {"row": row, "col": col, "value": value}
    print('here',item)
    wb = load_workbook(location+item_id)
    ws = wb.active 
    max_row=ws.max_row
    max_column=ws.max_column
    line='''
    <td><a href="/change/{}?row={}&col={}&value={}">{}</a></td>
    '''
    ws.cell(row=row, column=col,value=item['value'])
    wb.save(location+item_id)
    html=""
    #for row in range(1,max_row+1):
    #    html=html+"<tr>"
    for col in range(1,max_column+1):
        cell=ws.cell(row=row, column=col).value
        html=html+line.format(item_id,row,col,cell,cell)
    print(row)
    print(html)

    return  HTMLResponse(content=index.format(content.format(item_id,item_id,item_id)+html))

@app.get("/search/")
async def search0():
    filelist=os.listdir(location)
    html='<h2>EXCEL在线编辑DEMO</h2>'
    for file in filelist:
        html=html+'<h4><a href="/search/{}" style="text-align:center;">{}</a></h4>'.format(file,file)
    html=html+'<h4><a href="/upload" style="text-align:center;">上传新xlsx文件</a></h4>'
    
    return HTMLResponse(content=index.format(html))

@app.get("/search/{item_id}")
async def search(item_id: str,q: str=None):

    print(location+item_id)
    wb = load_workbook(location+item_id)
    ws = wb.active 
    max_row=ws.max_row
    max_column=ws.max_column

    if q:
        line='''
        <td>{}</td>
        '''
        rows_q=[]
        for row in range(1,max_row+1):
            #print(row)
            for col in range(1,max_column+1):
                cell=ws.cell(row=row, column=col).value
                if cell==q:
                    rows_q.append(row)
            line='''
        <td><a href="/change/{}?row={}&col={}&value={}">{}</a></td>
        '''
        html=""
        for row in rows_q:
            html=html+"<tr>"
            for col in range(1,max_column+1):
                cell=ws.cell(row=row, column=col).value
                html=html+line.format(item_id,row,col,cell,cell)
    else:
        line='''
        <td><a href="/change/{}?row={}&col={}&value={}">{}</a></td>
        '''
        html=""
        for row in range(1,max_row+1):
            html=html+"<tr>"
            for col in range(1,max_column+1):
                cell=ws.cell(row=row, column=col).value
                html=html+line.format(item_id,row,col,cell,cell)        

    return  HTMLResponse(content=index.format(content.format(item_id,item_id,item_id)+html))
    #return HTMLResponse(content=content)

@app.post("/uploadfiles/")
async def create_upload_files(
    file: UploadFile = File(...)
):
    #return {"filenames": [file.filename for file in files]}
    #contents = await files[0].read().decode('utf8')
    contents = file.file.read()
    name0=file.filename
    if name0.endswith('xlsx'):
        with open('static/'+name0,'wb') as f:
            f.write(contents)
    #return files[0].filename, files[0].content_type
        content = """
<body>
<a href="/" title="转到CSS5主页">上传完成，返回首页</a>
</body>
 """.format(name0)
    return HTMLResponse(content=content)


@app.get("/upload")
async def upload():
    content = """
<body>
<form action="/uploadfiles/" enctype="multipart/form-data" method="post">
<input name="file" type="file" multiple>
<input type="submit">
</form>
</body>
 """
    return HTMLResponse(content=content)

if __name__ == "__main__":

 

    
    filename=os.path.basename(sys.argv[0]).split('.')[0]
    uvicorn.run('{}:app'.format(filename), host="0.0.0.0",port=80,log_level="info",workers=5,reload=True)
